"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : drop table添加temporary语法，导出再导入
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库下创建表后使用drop temporary语法删表
    3.使用gs_dump工具导出纯文本格式
    4.gsql导入
    5.连接导入库查询表
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.表不存在
    6.成功
History     :
"""

import os
import unittest
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0050(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.db_name = "db_dump_0050"
        self.tb_name = "tb_dump_0050"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0050')
        self.default_value = self.com.show_param('support_extended_features')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('---修改参数为on---')
        msg = self.sh_primary.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'support_extended_features = on')
        self.log.info(msg)
        self.assertTrue(msg)
        msg = self.sh_primary.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_primary.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '--step2: 兼容b库下创建表后使用drop temporary语法删表 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(id int)compression=pglz;
        drop temporary table {self.tb_name};'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_result,
                      '执行失败:' + text)

        text = '----step3: 使用gs_dump工具导出纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port}  " \
                      f"{self.db_name} " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully", dump_msg,
                      '执行失败:' + text)

        text = '----step4: gsql导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gsql " \
                   f"-p {self.dbuser_node.db_port} " \
                   f"{self.db_name}_01 " \
                   f"-f {self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertNotIn('CREATE TABLE', gsql_msg)

        text = '----step5: 连接导入库查询表 expect:表不存在----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn("ERROR:  Table 'public.tb_dump_0050' doesn't exist",
                      sql_result, '执行失败:' + text)

    def tearDown(self):
        text = '---step6: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        msg = self.sh_primary.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'support_extended_features '
                                            f'= {self.default_value}')
        self.log.info(msg)
        self.assertTrue(msg)
        msg = self.sh_primary.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_primary.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
